\documentclass{article}
\usepackage[utf8]{inputenc}
\usepackage{listings}
\usepackage[most]{tcolorbox}
\usepackage{geometry} % set margin
\geometry{left=3cm, right=2.5cm, top=2.5cm, bottom=2.5cm}

\newtcblisting{commandshell}{colback=white,colupper=black,colframe=black!75!black,
listing only,listing options={language=sh, breaklines=true,aboveskip=0pt, belowskip=0pt},
every listing line={\small\ttfamily\bfseries{[oracle@tp-shchai]\$} }}

\newtcblisting{sqlshell}{colback=white,colupper=black,colframe=black!75!black,
listing only,listing options={language=SQL, breaklines=true, aboveskip=0pt, belowskip=0pt},
every listing line={\small\ttfamily\bfseries{SQL> }}}

\newtcblisting{messageshell}{colback=white,colupper=black,colframe=black!75!black,
listing only,listing options={language={}, basicstyle=\small\ttfamily, breaklines=true,aboveskip=0pt, belowskip=0pt},
every listing line={}}

\title{Database Management Experiment Report \#1}
\author{Shitong CHAI}
\date{\vspace{-5ex}}
\begin{document}
\maketitle

\section{Analysis of an Existing Oracle instance}

\begin{enumerate}


%%%%%%%%%%%%%%%%%%%%% 1
\item List the instance name, database name and the value of data block size:


\begin{itemize}

\item Instance name:
\begin{sqlshell}
show parameter instance_name;
\end{sqlshell}

\begin{messageshell}

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      ORCLCDB

\end{messageshell}

\item Database name:
\begin{sqlshell}
show parameter db_name;
\end{sqlshell}

\begin{messageshell}
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      ORCLCDB
\end{messageshell}

\item Data block size:
\begin{sqlshell}
show parameter db_block_size;
\end{sqlshell}
\begin{messageshell}
      
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

\end{messageshell}

\end{itemize}

%%%%%%%%%%%%%%%%%%%%%%%%%%%2
\item What is the size of the SGA ?
\begin{sqlshell}
select name,value/1024/1024 "SGA(MB)" from v$sga;
\end{sqlshell}
\begin{messageshell}

NAME                 SGA(MB)
-------------------- ----------
Fixed Size           8.48838043
Variable Size        568
Database Buffers     152
Redo Buffers         7.5078125
\end{messageshell}

%%%%%%%%%%%%%%%%%%%%%%3
\item List the name and the size of data files, log files and the name of control files.
\begin{itemize}

\item Data files:
\begin{sqlshell}
select name, bytes/1024/1024 "size(MB)"  from V$datafile;
\end{sqlshell}
\begin{messageshell}
NAME
--------------------------------------------------------------------------------
  size(MB)
----------
/opt/oracle/oradata/ORCLCDB/system01.dbf
       860

/opt/oracle/oradata/ORCLCDB/sysaux01.dbf
       780

/opt/oracle/oradata/ORCLCDB/undotbs01.dbf
       310


NAME
--------------------------------------------------------------------------------
  size(MB)
----------
/opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
       270

/opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
       360

/opt/oracle/oradata/ORCLCDB/users01.dbf
    5


NAME
--------------------------------------------------------------------------------
  size(MB)
----------
/opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
       100

/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
       270

/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
       370


NAME
--------------------------------------------------------------------------------
  size(MB)
----------
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
       100

/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
    5


11 rows selected.

\end{messageshell}

\item Log files:
\begin{sqlshell}
select v$logfile.member, v$log.bytes/1024/1024 "size(MB)" from v$logfile join v$log on v$logfile.group#=v$log.group#;
\end{sqlshell}
\begin{messageshell}

MEMBER
--------------------------------------------------------------------------------
  size(MB)
----------
/opt/oracle/oradata/ORCLCDB/redo03.log
       200

/opt/oracle/oradata/ORCLCDB/redo02.log
       200

/opt/oracle/oradata/ORCLCDB/redo01.log
       200


\end{messageshell}

\item Control files:
\begin{sqlshell}
show parameters control_files;
\end{sqlshell}
\begin{messageshell}
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                          string        /opt/oracle/oradata/ORCLCDB/control01.ctl, /opt/oracle/oradata/ORCLCDB/control02.ctl

\end{messageshell}
\end{itemize}
%%%%%%%%%%%%%%%%%%%%4
\item Display the version number.
\begin{sqlshell}
select banner from v$version;
\end{sqlshell}
\begin{messageshell}

BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

\end{messageshell}

%%%%%%%%%%%%%%%%%%%%5
\item Give the maximum number of user processes which can connect simultaneously to the instance.
\begin{sqlshell}
select value from v$parameter where name = 'processes';
\end{sqlshell}
\begin{messageshell}

VALUE
--------------------------------------------------------------------------------
300


\end{messageshell}
%%%%%%%%%%%%%%%%%%%6
\item Stop the instance
\begin{sqlshell}
shutdown;
\end{sqlshell}
\begin{messageshell}
Database closed.
Database dismounted.
ORACLE instance shut down.
\end{messageshell}
%%%%%%%%%%%%%%%%%%%7
\item Restart the instance.
\begin{sqlshell}
startup;
\end{sqlshell}
\begin{messageshell}
ORACLE instance started.

Total System Global Area  771747944 bytes
Fixed Size                  8900712 bytes
Variable Size                633339904 bytes
Database Buffers         121634816 bytes
Redo Buffers                  7872512 bytes
Database mounted.
Database opened.
\end{messageshell}

\end{enumerate}
% \begin{sqlshell}
% \end{sqlshell}
% \begin{messageshell}
% \end{messageshell}

\section{Stopping and starting an instance}

\begin{enumerate}
  \item Try to change the value of the data block size and observe what happens.
\begin{sqlshell}
startup pfile=$HOME/myinit.ora;
\end{sqlshell}
\begin{messageshell}
ORACLE instance started.

Total System Global Area  771747944 bytes
Fixed Size                  8900712 bytes
Variable Size                612368384 bytes
Database Buffers         142606336 bytes
Redo Buffers                  7872512 bytes
ORA-00058: DB_BLOCK_SIZE must be 8192 to mount this database (not 16384)

\end{messageshell}
  \item Open a second session as administrator and try to stop the database in “normal” mode. 
\begin{sqlshell}
@http://fc.isima.fr/~kang/adminoracleFI/create_scott.sql
\end{sqlshell}
\begin{messageshell}
Session altered.

User created.

Grant succeeded.

Connected.

Table created.

1 row created.

1 row created.

1 row created.

1 row created.

Commit complete.

\end{messageshell}
\begin{commandshell}
sqlplus scott/tiger
\end{commandshell}
\begin{sqlshell}
insert into emp values
(100,'toto','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
\end{sqlshell}

\begin{messageshell}

1 row created.

\end{messageshell}

In the administrator session:
\begin{sqlshell}
shutdown;
\end{sqlshell}

The administrator’s session is locked 

  \item Try to stop the database now with the “immediate” shutdown mode
  \begin{sqlshell}
shutdown immediate;
\end{sqlshell}
\begin{messageshell}
ORA-01097: cannot shutdown while in a transaction - commit or rollback first
\end{messageshell}
  \item Open a new sqlplus session as scott/tiger and check what happened 
\begin{sqlshell}
shutdown abort;
\end{sqlshell}
\begin{messageshell}
ORACLE instance shut down.
\end{messageshell}
\begin{sqlshell}
startup;
\end{sqlshell}
\begin{commandshell}
sqlplus scott/tiger
\end{commandshell}
\begin{sqlshell}
select * from emp;
\end{sqlshell}
\begin{messageshell}
EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK             7902 17-DEC-80           800
       20

      7499 ALLEN      SALESMAN             7698 20-FEB-81          1600        300
       30

      7521 WARD       SALESMAN             7698 22-FEB-81          1250        500
       30


     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER             7839 02-APR-81          2975
       20


\end{messageshell}
The "toto" entry disappeared.
  \item From the SYS session, stop the instance in transaction mode 
In the administrator's session:
  \begin{sqlshell}
insert into emp values
(200,'titi','MANAGER',7840,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
\end{sqlshell}
\begin{messageshell}
1 row created.
\end{messageshell}

\begin{sqlshell}
shutdown transactional;
\end{sqlshell}
The administrator's session is locked.
  \item Open a new shell window and try logging in as scott/tiger. 
  \begin{commandshell}
sqlplus scott/tiger
\end{commandshell}
\begin{messageshell}

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Oct 19 18:32:34 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

ERROR:
ORA-01089: immediate shutdown or close in progress - no operations are
permitted
Process ID: 0
Session ID: 0 Serial number: 0


\end{messageshell}
  \item Validate the transaction with the command “commit”. What happens at the SYS session from which you ran “shutdown transactional” ?
  \begin{sqlshell}
commit;

\end{sqlshell}
\begin{messageshell}
Commit complete.

\end{messageshell}
In the administrator's session:
\begin{messageshell}
Database closed.
Database dismounted.
ORACLE instance shut down.

\end{messageshell}
\end{enumerate}

\end{document}
